In this module, you helped little Lemon to create sales
reports from the data in their database. You also helped them to build
a table booking system. Let's take a few minutes to
recap the tasks, processes and tools you completed or
made use of in this module. In the first task you
created a sales report for a Little Lemon by querying the data in
their database using virtual tables, JOINS, stored procedures and
prepared statements. You helped Little Lemon to query their
database using a virtual table to make use of data that exists in other tables. The benefits of the virtual table are that
you are able to use it to simplify data access inquiries, create a JOIN
from virtual and base tables, efficiently manipulate and filter data,
and support database security. You also made use of the JOIN clause
to link records of data between one or more tables based on a common column. There are several types of JOINS that you
were able to make use of in this module, including inner join,
left join and right join. You also had the opportunity to use
the self join and the full outer join. You also help Little Lemon
to use stored procedures. Stored procedures are used to create
reusable code that can be invoked and executed efficiently. By using these procedures,
you made your code more consistent, reusable and easier to use and maintain. So instead of typing the same code
repeatedly, you're able to save your blocks of code as stored procedures that
you could then invoke when required. You were able to create many
stored procedures,, and you could include multiple
parameters in each one. You are also able to include a range of
syntax like sequel statements, variables and control structures, while making
sure that each one had a unique name. And how you created your stored
procedures depended on the tasks that you needed to achieve. You also assisted Little Lemon with
the use of prepared statements. Each time little Lemon create SQL
statements, they need to be compiled and parsed by MySQL before
they can be executed. You showed them that a more efficient
method is to create a prepared statement that can be used repeatedly
without the need for compiling. Prepared statements
are a much more efficient and optimal way of executing statements
without using valuable MySQL resources. MySQL compiles and parses a prepared
statement just once before it's executed. In the second lesson of this module you
help Little Lemon to build a table booking system in their database, that they could use to keep track
of guests visiting the restaurant. You were able to help
Little Lemon develop and populate their table booking system by
creating data in the form of new bookings. You created data using standard insert
into statements while identifying the following within your syntax,
the tables you wanted to create the data within, the columns to be populated, and
the values that they needed to contain. You then execute your insert into
statements to create the data within your database. There were also instances in which the
data that you originally created needed to change. You were able to carry out these actions
using update and delete statements. You used an update query
to alter information within the table while identifying key
information in each of your queries. When you were deleting or
dropping information from the table, you used a delete query, again making sure
to identify key information in your query. Once you created updated or
deleted data within the booking table, you ran tests to make sure that your
queries were executed successfully. You carried out these tests using
read queries like select statements. In this case you made sure that your
select statements contained the following, the name of the table and columns that
held the data, the values you required, and any conditions required
to help you target the data. You also made use of triggers to store
a set of actions in the form of a stored program that you could then invoke
automatically when certain events occur. To use triggers, you first created them
using the create trigger statement. Then you define the trigger type. For example, you specified if they were
insert, update or delete triggers, and if they should be executed before or
after the event. You also defined the logic
of your triggers, specified which tables they were assigned to, and
how they should be applied to the table. Once you're confident that
your code was correct, you then committed your progress
to get an enacted version control. You have helped Little Lemon to create
sales reports and a table booking system using database queries,
procedures and prepared statements. Well done, I look forward to providing you
with more guidance in the next module.